﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;


namespace FrameWork.Lib
{
    public static class SQLExt
    {
        public static string DBString(this string pValue)
        {
            if (pValue.IsEmpty())
            {
                return " NULL ";
            }

            return "'" + pValue.Replace("'", "''") + "'";

        }

        public static string DBString(this string pValue, int pMaxLength)
        {
            if (pValue.IsEmpty())
            {
                return " NULL ";
            }
            else
            {
                if (pValue.Length > pMaxLength)
                {
                    return "'" + pValue.Substring(0, pMaxLength).Replace("'", "''") + "'";
                }
                else
                {
                    return "'" + pValue.Replace("'", "''") + "'";
                }

            }

        }

        public static string DBString(this DateTime pValue)
        {
            try
            {
                return "'" + System.Data.SqlTypes.SqlDateTime.Parse(pValue.ToString()).ToString() + "'";
            }
            catch
            {
                return " NULL ";
            }
        }

        public static string DBString(this DateTime? pValue)
        {
            if (pValue == null)
            {
                return " NULL ";
            }
            return pValue.Value.DBString();

        }

        public static string DBString(this double pValue)
        {
            return "'" + pValue.ToString() + "'";
        }

        public static string DBString(this int pValue)
        {
            return "'" + pValue.ToString() + "'";
        }

        public static string DBString(this double? pValue)
        {
            if (pValue == null)
            {
                return " NULL ";
            }

            return pValue.Value.DBString();
        }

        public static string DBString(this int? pValue)
        {
            if (pValue == null)
            {
                return " NULL ";
            }

            return pValue.Value.DBString();
        }

        public static string DBString(this bool? pValue)
        {
            if (pValue == null)
            {
                return " NULL ";
            }

            return pValue.Value.DBString();
        }

        public static string DBString(this bool pValue)
        {
            if (pValue)
            {
                return " '1' ";
            }

            return "'0'";
        }

        public static string SQLDateTimeDBValue(this string pValue)
        {
            string tResult = " NULL ";
            if (!pValue.IsEmpty())
            {
                object tValue = pValue.SqlDateTimeSringOrDBNull();
                if (tValue != DBNull.Value)
                {
                    tResult = "'" + tValue.ToString() + "'";
                }
            }
            return tResult;
        }

        public static object SqlDateTimeSringOrDBNull(this string pValue)
        {
            try
            {
                return System.Data.SqlTypes.SqlDateTime.Parse(pValue).ToString();
            }
            catch
            {
                return DBNull.Value;
            }
        }

        public static DateTime? SqlDateTimeOrNull(this object pValue)
        {
            DateTime? tResult = null;

            if (pValue is DateTime || pValue is DateTime?)
            {
                if (pValue != null)
                    tResult = (DateTime)pValue;
            }
            else
            {
                if (!pValue.IsEmpty())
                    tResult = pValue.ToString().DateValueOrNull();
            }

            if (tResult != null && (tResult.Value < DateTime.Parse("1/1/1753 12:00:00 AM") ||
                tResult.Value > DateTime.Parse("12/31/9999 11:59:59 PM")))
            {
                tResult = null;
            }

            return tResult;

        }
    }
}
